﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

public class CustomDbContext
{
    private readonly string _connectionString;

    public CustomDbContext(string connectionString)
    {
        _connectionString = connectionString;
    }

    // 重载方法：直接映射查询结果
    public async Task<List<T>> ExecuteSqlQueryAsync<T>(string sql, params OracleParameter[] parameters) where T : new()
    {
        var propertyColumnMap = EntityHelper.GetPropertyDatabaseColumnMap<T>();
        LogSqlCommand(sql, parameters);

        var result = new List<T>();

        using (var connection = new OracleConnection(_connectionString))
        {
            await connection.OpenAsync();

            using (var command = new OracleCommand(sql, connection))
            {
                command.Parameters.AddRange(parameters);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    var entityPropertyColumnMap = EntityHelper.GetPropertyColumnMap<T>();
                    while (await reader.ReadAsync())
                    {
                        result.Add(EntityHelper.MapReaderToEntity<T>(reader, entityPropertyColumnMap));
                    }
                }
            }
        }

        return result;
    }

    // 重载方法：通过 Func<IDataReader, T> 映射查询结果
    public async Task<List<T>> ExecuteSqlQueryAsync<T>(string sql, Func<IDataReader, T> map, params OracleParameter[] parameters)
    {
        LogSqlCommand(sql, parameters);

        var result = new List<T>();

        using (var connection = new OracleConnection(_connectionString))
        {
            await connection.OpenAsync();

            using (var command = new OracleCommand(sql, connection))
            {
                command.Parameters.AddRange(parameters);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        result.Add(map(reader));
                    }
                }
            }
        }

        return result;
    }

    public async Task<int> ExecuteScalarAsync(string sql, params OracleParameter[] parameters)
    {
        LogSqlCommand(sql, parameters);

        using (var connection = new OracleConnection(_connectionString))
        {
            await connection.OpenAsync();

            using (var command = new OracleCommand(sql, connection))
            {
                command.Parameters.AddRange(parameters);
                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result);
            }
        }
    }


    public async Task<List<T>> GetPartialAsync<T>(string sql, params string[] columns) where T : new()
    {
        var propertyColumnMap = EntityHelper.GetPropertyDatabaseColumnMap<T>();
        foreach (var column in columns)
        {
            if (propertyColumnMap.TryGetValue(column, out var dbColumn))
            {
                sql = sql.Replace(column, dbColumn);
            }
        }

        return await ExecuteSqlQueryAsync<T>(sql, parameters: null);
    }

    public async Task<List<T>> GetAllFieldsAsync<T>(string sql) where T : new()
    {
        return await ExecuteSqlQueryAsync<T>(sql, parameters: null);
    }

    private void LogSqlCommand(string sql, OracleParameter[] parameters)
    {
        Console.WriteLine($"Executing SQL: {sql}");
        if (parameters != null && parameters.Length > 0)
        {
            Console.WriteLine("Parameters:");
            foreach (var param in parameters)
            {
                Console.WriteLine($"  {param.ParameterName}: {param.Value}");
            }
        }
    }
}
